﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.ComponentModel;
using System.Text;
//librerias especiales para la BD
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data.OleDb;

namespace CityGames
{
    public class CADSocio
    {
        static private string cadConexion = System.Configuration.ConfigurationManager.ConnectionStrings["CityGames.Properties.Settings.BDCityGamesConnectionString"].ConnectionString;

        public DataSet ObtenerDatos(string select, string nombreTabla)
        {
            DataSet resultado = new DataSet();

            try
            {
                SqlConnection conexion = new SqlConnection(cadConexion);
                SqlDataAdapter da = new SqlDataAdapter(select, conexion);
                da.Fill(resultado, nombreTabla); 
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return resultado;
        }

        public bool InsertarDatosModificados(DataSet datosModificados, string nombreTabla, string select)
        {
            bool resultado = false;

            try
            {
                SqlConnection conexion = new SqlConnection(cadConexion);
                SqlDataAdapter da = new SqlDataAdapter(select, conexion);
                SqlCommandBuilder cb = new SqlCommandBuilder(da);
                da.Update(datosModificados, nombreTabla);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return resultado;
        }

        public bool BorrarSocio(string dni)
        {
            bool resultado = false;
            SqlConnection conexion = null;
            SqlCommand comandoSql;
            string delete = "";

            try
            {
                conexion = new SqlConnection(cadConexion);
                conexion.Open();

                delete = "DELETE FROM Persona WHERE (DNI = '" + dni + "')";

                comandoSql = new SqlCommand(delete, conexion);
                comandoSql.ExecuteNonQuery();

                resultado = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conexion != null)
                    conexion.Close();
            }

            return resultado;
        }

        public DataSet FiltrarSocios(string nombre, string apellidos, string dni, string sexo, int desde, int hasta)
        {
            DataSet resultado = new DataSet();
            string select = "";

            try
            {
                select += "SELECT   Nombre, Apellidos, DNI, Sexo, FechaNac, Direccion, Localidad, Provincia, CP, Telefono, E_mail, Foto ";
                select += "FROM         (   SELECT     Nombre, Apellidos, S.DNI, Sexo, FechaNac, Direccion, Localidad, Provincia, CP, Telefono, E_mail, Foto,  ROW_NUMBER() OVER (ORDER BY Nombre ASC) AS ROW ";
                select += "FROM     Socio S, Persona P ";
                select += "WHERE    S.DNI=P.DNI ";
                if (nombre != "")
                    select += "AND (Nombre LIKE '%" + nombre + "%') ";
                if (apellidos != "")
                    select += "AND (Apellidos LIKE '%" + apellidos + "%') ";
                if (dni != "")
                    select += "AND (S.DNI LIKE '%" + dni + "%')";
                if (sexo == "Hombre")
                    select += " AND (Sexo = 'Hombre')";
                else if (sexo == "Mujer")
                    select += " AND (Sexo = 'Mujer')";    
                select += ") AS SociosFiltrados "; 
                select += "WHERE     (ROW > " + desde.ToString() + ") AND (ROW <= " + hasta.ToString() + ") ";

                resultado = ObtenerDatos(select, "SociosFiltrados");

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return resultado;
        }

        public int ObtenerNumeroDeSociosFiltrados(string nombre, string apellidos, string dni, string sexo)
        {
            int resultado = 0;
            SqlConnection c = null;

            string select = "SELECT count(*) as Resultado FROM Socio S, Persona P WHERE S.DNI=P.DNI ";
            if(nombre != "")
                select += "AND (Nombre LIKE '%" + nombre + "%') ";
            if(apellidos != "")
                select += "AND (Apellidos LIKE '%" + apellidos + "%') ";
            if(dni != "")
                select += "AND (S.DNI LIKE '%" + dni + "%')";
            
            if (sexo == "Hombre")
                select += " AND (Sexo = 'Hombre')";
            else if (sexo == "Mujer")
                select += " AND (Sexo = 'Mujer')";

            try
            {
                c = new SqlConnection(cadConexion);
                c.Open();
                SqlCommand command = new SqlCommand(select, c);
                SqlDataReader dr = command.ExecuteReader();
                dr.Read();

                resultado = int.Parse(dr["Resultado"].ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                c.Close();
            }
            return resultado;
        }

        public bool UpdateSocio(DataRow dr)
        {
            bool resultado = false;
            SqlConnection conexion = null;
            SqlTransaction tr = null;
            SqlCommand comandoSql;
            string update = "";


            string nombre = dr["Nombre"].ToString();
            string apellidos = dr["Apellidos"].ToString();
            string sexo = dr["Sexo"].ToString();
            string telefono = dr["Telefono"].ToString();
            string email = dr["E_mail"].ToString();

            string dni = dr["DNI"].ToString();
            string fecha = dr["FechaNac"].ToString();
            string direccion = dr["Direccion"].ToString(); 
            string localidad = dr["Localidad"].ToString();
            string provincia = dr["Provincia"].ToString();
            string cp = dr["CP"].ToString();
            string foto = dr["Foto"].ToString();

            try
            {
                update += "UPDATE Persona ";
                update += "SET Nombre = '" + nombre + "', Apellidos = '" + apellidos + "', DNI = '" + dni + "', Sexo = '" + sexo + "', Telefono = '" + telefono + "', E_mail = '" + email + "' ";
                update += "WHERE DNI ='" + dni +"';";

                update += "UPDATE Socio ";
                update += "SET DNI = '" + dni + "', FechaNac = '" + fecha + "', Direccion = '" + direccion + "', Localidad = '" + localidad + "', Provincia = '" + provincia + "', CP = '" + cp + "', Foto = '" + foto + "' ";
                update += "WHERE DNI ='" + dni + "';";

                conexion = new SqlConnection(cadConexion);
                conexion.Open();

                tr = conexion.BeginTransaction();

                comandoSql = new SqlCommand(update, conexion);
                comandoSql.Transaction = tr;
                comandoSql.ExecuteNonQuery();

                tr.Commit();
                resultado = true;
            }
            catch (Exception ex)
            {
                tr.Rollback();
                throw ex;
            }
            finally
            {
                if (conexion != null)
                    conexion.Close();
            }

            return resultado;
        }

        public string getNombreyApellidos(string DNI)
        {
            string resultado = "";
            SqlConnection c = null;
            string select = "SELECT Nombre, Apellidos FROM Persona WHERE DNI='" + DNI + "';";

            try
            {
                c = new SqlConnection(cadConexion);
                c.Open();
                SqlCommand command = new SqlCommand(select, c);
                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                    resultado = dr["Nombre"].ToString() + " " + dr["Apellidos"].ToString();
                else
                    resultado = "";
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                c.Close();
            }

            return resultado;
        }

        public string ObtenerRutaFoto(string DNI)
        {
            string resultado = "";
            SqlConnection c = null;
            string select = "SELECT Foto FROM Socio WHERE DNI='" + DNI + "';";

            try
            {
                c = new SqlConnection(cadConexion);
                c.Open();
                SqlCommand command = new SqlCommand(select, c);
                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                    resultado = dr["Foto"].ToString();
                else
                    resultado = "C:\\Fotos\\defecto.jpg";

                if (resultado == "")
                    resultado = "C:\\Fotos\\defecto.jpg";
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                c.Close();
            }

            return resultado;        
        }

        

        public bool InsertarSocio(string nombre, string apellidos, string dni, string sexo, DateTime fecha, string direccion, string localidad, string provincia, string cp, string telefono, string email, string foto)
        {
            bool resultado = false;
            SqlConnection conexion = null;
            SqlTransaction tr = null;
            SqlCommand comandoSql = null;
            SqlParameter paramNombre = null;
            SqlParameter paramApellidos = null;
            string insert = "";

            try
            {
                paramNombre = new SqlParameter("@nombre", System.Data.SqlDbType.VarChar, 50);
                paramNombre.Value = nombre;

                paramApellidos = new SqlParameter("@apellidos", System.Data.SqlDbType.VarChar, 100);
                paramApellidos.Value = apellidos;

                insert += "INSERT INTO Persona (Nombre, Apellidos, Dni, Sexo, Telefono, E_mail) ";
                insert += "VALUES (@nombre, @apellidos, '" + dni + "', '" + sexo + "', '" + telefono + "', '" + email + "'); ";
                insert += "INSERT INTO Socio (Dni, FechaNac, Direccion, Localidad, Provincia, CP, Foto, Deuda) ";
                insert += "VALUES ('" + dni + "', '" + fecha + "', '" + direccion + "', '" + localidad + "', '" + provincia + "', '" + cp + "','" + foto + "','" + "0" + "')";

                conexion = new SqlConnection(cadConexion);
                conexion.Open();

                tr = conexion.BeginTransaction();

                comandoSql = new SqlCommand(insert, conexion);
                comandoSql.Transaction = tr;

                comandoSql.Parameters.Add(paramNombre);
                comandoSql.Parameters.Add(paramApellidos);
                
                comandoSql.ExecuteNonQuery();

                tr.Commit();
                resultado = true;
            }
            catch (Exception ex)
            {
                tr.Rollback();
                throw ex;
            }
            finally
            {
                if (conexion != null)
                    conexion.Close();
            }

            return resultado;
        }

        public bool ExisteSocio(string dni, string letra)
        {
            bool resultado = false;
            SqlConnection conexion = null;

            try
            {
                string select = "SELECT Dni FROM Socio WHERE (Dni = '" + dni + "" + letra + "')";
                conexion = new SqlConnection(cadConexion);
                conexion.Open();

                SqlCommand comandoSql = new SqlCommand(select, conexion);
                SqlDataReader dr = comandoSql.ExecuteReader();

                if (dr.Read())
                    resultado = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conexion != null)
                    conexion.Close();
            }

            return resultado;
        }

        public DataSet ObtenerDatosSocio(string dni)
        {
            DataSet datosSocio = new DataSet();
            string select = "";

            try
            {
                select += " SELECT        Nombre, Apellidos, CP, Localidad, Provincia, Direccion ";
                select += " FROM            Persona P, Socio S ";
                select += " WHERE P.DNI = S.DNI ";
                select += " AND S.DNI = '" + dni + "' ";

                datosSocio = ObtenerDatos(select, "DatosSocio");
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return datosSocio;
        }
        /************************************************** Funciones de Ruben **************************************************/

        /// <summary>
        /// Función que devuelve la deuda de un socio
        /// </summary>
        /// <param name="DNI">
        /// El dni del socio del cual se quiere saber la deuda</param>        
        /// <returns>
        /// La deuda del socios</returns>
        public double getDeuda(string DNI)
        {
            double resultado = 0;
            SqlConnection c = null;
            string select = "SELECT Deuda FROM Socio WHERE DNI='" + DNI + "' AND (Deuda IS NOT NULL);";

            try
            {
                c = new SqlConnection(cadConexion);
                c.Open();
                SqlCommand command = new SqlCommand(select, c);
                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                    resultado = double.Parse(dr["Deuda"].ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                c.Close();
            }

            return resultado;
        }

        /// <summary>
        /// Función que añade una cantidad a la deuda de un socio
        /// </summary>
        /// <param name="deudaMas">
        /// La cantidad que se desea incrementar la deuda</param>
        /// <param name="dni">
        /// El dni del socio</param>
        /// <returns>
        /// Un booleano que indica si se ha incrementado la deuda correctamente</returns>
        public bool SumarADeuda(decimal deudaMas, string dni)
        {
            bool ok = false;
            decimal deuda = 0;                
            SqlConnection conexion = null;
            SqlTransaction tr = null;
            SqlCommand comandoSql;
            string update = "";
           

            try
            {
                deuda = decimal.Parse(getDeuda(dni).ToString());
                deuda += deudaMas;
                SqlParameter param = new SqlParameter("@deuda", deuda);

                conexion = new SqlConnection(cadConexion);
                conexion.Open();

                update = "UPDATE Socio ";
                update += "SET Deuda = @deuda ";
                update += "WHERE (DNI = '" + dni + "')";

                tr = conexion.BeginTransaction();

                comandoSql = new SqlCommand(update, conexion);
                comandoSql.Parameters.Add(param);
                comandoSql.Transaction = tr;
                comandoSql.ExecuteNonQuery();

                tr.Commit();
                ok = true;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conexion != null)
                    conexion.Close();
            }

            return ok;
        }

        /// <summary>
        /// Función que ejecuta la consulta pasada por parámetro en Modo Desconectado y devuelve un DataSet con el resultado en una tabla denominada con el nombre especificado
        /// </summary>
        /// <param name="dni">
        /// El dni del socio</param>        
        /// <returns>
        /// Devuelve un booleano que indica si se ha borrado la deuda correctamente</returns>
        public bool DeudaACero(string dni)
        {
            bool ok = false;
            SqlConnection conexion = null;
            SqlTransaction tr = null;
            SqlCommand comandoSql;
            string update = "";


            try
            {
                conexion = new SqlConnection(cadConexion);
                conexion.Open();

                update = "UPDATE Socio ";
                update += "SET Deuda = '0' ";
                update += "WHERE (DNI = '" + dni + "')";

                tr = conexion.BeginTransaction();

                comandoSql = new SqlCommand(update, conexion);
                comandoSql.Transaction = tr;
                comandoSql.ExecuteNonQuery();

                tr.Commit();
                ok = true;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conexion != null)
                    conexion.Close();
            }

            return ok;
        }
        /**************************************************Fin Funciones de Ruben **************************************************/

        //Gonzalo Web
        public bool DatosSocio(ref string dni, ref string nombre, ref string apellidos, ref string nick, ref string password, ref string email, ref string avatar, ref string sexo, ref string localidad, ref string provincia, ref string direccion, ref string cp, ref string telefono)
        {
            bool resultado = false;

            SqlConnection c = null; 
            string select = "";
            select += "SELECT s.DNI, Nombre, Apellidos, UsuarioWeb, Password, E_mail, Avatar, Sexo, Localidad, Provincia, Direccion, CP, Telefono ";
            select += "FROM Socio s, Persona p ";
            select += " WHERE p.DNI = s.DNI ";
            select += " AND s.DNI = '" + dni + "' ";

            try
            {
                c = new SqlConnection(cadConexion);
                c.Open();
                SqlCommand command = new SqlCommand(select, c);
                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                {
                    resultado = true;
                    dni = dr["DNI"].ToString();
                    nombre = dr["Nombre"].ToString();
                    apellidos = dr["Apellidos"].ToString();
                    nick = dr["UsuarioWeb"].ToString();
                    password = dr["Password"].ToString();
                    email = dr["E_mail"].ToString();
                    avatar = dr["Avatar"].ToString();
                    sexo = dr["Sexo"].ToString();
                    localidad = dr["Localidad"].ToString();
                    provincia = dr["Provincia"].ToString();
                    direccion = dr["Direccion"].ToString();
                    cp = dr["Cp"].ToString();
                    telefono = dr["Telefono"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                c.Close();
            }

            return resultado;
        }

        public bool InsertarSocioWeb(string nombre, string apellidos, string dni, string nick, string password, string email, string avatar, string sexo, string direccion, string cp, string localidad, string provincia, string telefono)
        {
            bool resultado = false;
            SqlConnection conexion = null;
            SqlTransaction tr = null;
            SqlCommand comandoSql = null;
            SqlParameter paramNombre = null;
            SqlParameter paramApellidos = null;
            string insert = "";

            try
            {
                paramNombre = new SqlParameter("@nombre", System.Data.SqlDbType.VarChar, 50);
                paramNombre.Value = nombre;

                paramApellidos = new SqlParameter("@apellidos", System.Data.SqlDbType.VarChar, 100);
                paramApellidos.Value = apellidos;

                insert += "INSERT INTO Persona (Nombre, Apellidos, Dni, Sexo, E_mail, Telefono) ";
                insert += "VALUES (@nombre, @apellidos, '" + dni + "', '" + sexo + "', '" + email + "', '" + telefono + "'); ";
                insert += "INSERT INTO Socio (Dni, UsuarioWeb, Password, Direccion, Cp, Localidad, Provincia, Avatar) ";
                insert += "VALUES ('" + dni + "', '" + nick + "', '" + password + "', '" + direccion + "', '" + cp + "', '" + localidad + "', '" + provincia + "','" + avatar + "')";

                conexion = new SqlConnection(cadConexion);
                conexion.Open();

                tr = conexion.BeginTransaction();

                comandoSql = new SqlCommand(insert, conexion);
                comandoSql.Transaction = tr;

                comandoSql.Parameters.Add(paramNombre);
                comandoSql.Parameters.Add(paramApellidos);

                comandoSql.ExecuteNonQuery();

                tr.Commit();
                resultado = true;
            }
            catch (Exception ex)
            {
                tr.Rollback();
                throw ex;
            }
            finally
            {
                if (conexion != null)
                    conexion.Close();
            }

            return resultado;
        }

        public bool UpdateSocioWeb(string nombre, string apellidos, string dni, string nick, string email, string avatar, string sexo, string direccion, string cp, string localidad, string provincia, string telefono)
        {
            bool resultado = false;
            SqlConnection conexion = null;
            SqlTransaction tr = null;
            SqlCommand comandoSql = null;
            string update = "";

            try
            {
                update += "UPDATE Persona ";
                update += "SET Nombre = '" + nombre + "', Apellidos = '" + apellidos + "', Sexo = '" + sexo + "', E_mail = '" + email + "', Telefono = '" + telefono + "' ";
                update += "WHERE DNI ='" + dni + "';";

                update += "UPDATE Socio ";
                update += "SET UsuarioWeb = '" + nick + "', Direccion = '" + direccion + "',  Cp = '" + cp + "', Localidad = '" + localidad + "', Provincia = '" + provincia + "', Avatar = '" + avatar + "' ";
                update += "WHERE DNI ='" + dni + "';";

                conexion = new SqlConnection(cadConexion);
                conexion.Open();

                tr = conexion.BeginTransaction();

                comandoSql = new SqlCommand(update, conexion);
                comandoSql.Transaction = tr;

                comandoSql.ExecuteNonQuery();

                tr.Commit();
                resultado = true;
            }
            catch (Exception ex)
            {
                tr.Rollback();
                throw ex;
            }
            finally
            {
                if (conexion != null)
                    conexion.Close();
            }

            return resultado;
        }

        /******************Funciones Web mirellys*********************/
        public string getPasswordSocio(string dni)
        {
            string resultado = "";
            string select = "";

            SqlConnection c = null;
            SqlCommand command = null;
            SqlDataReader dr = null;

            try
            {
                c = new SqlConnection(cadConexion);
                c.Open();

                select = "SELECT Password FROM Socio WHERE DNI = @dni";

                command = new SqlCommand(select, c);

                command.Parameters.Add("@dni", SqlDbType.VarChar, 9);
                command.Parameters["@dni"].Value = dni;

                dr = command.ExecuteReader();

                if (dr.Read())
                    resultado = dr["Password"].ToString();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (c != null)
                    c.Close();
            }

            return resultado;
        }

        public bool ExisteSocioWeb(string dni)        
        {
            bool resultado = false;
            SqlConnection conexion = null;
            SqlCommand comandoSql = null;
            SqlDataReader dr = null;

            try
            {
                conexion = new SqlConnection(cadConexion);
                conexion.Open();

                string select = "SELECT DNI FROM Socio WHERE DNI = @dni";

                comandoSql = new SqlCommand(select, conexion);

                comandoSql.Parameters.Add("@dni", SqlDbType.VarChar, 9);
                comandoSql.Parameters["@dni"].Value = dni;

                dr = comandoSql.ExecuteReader();

                if (dr.Read())
                    resultado = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conexion != null)
                    conexion.Close();
            }

            return resultado;
        }
        /******************Fin Funciones Web mirellys*********************/
    }
}
